package com.project.dao.imp;

import com.project.bean.CutPageBean;
import com.project.bean.ProductBean;
import com.project.dao.IProductDao;
import org.springframework.stereotype.Repository;

import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

@Repository
public class ProductDaoImp extends BaseDao implements IProductDao {
    @Override
    public CutPageBean<ProductBean> findByItem(int pageNO, String name, LocalDate startDate, LocalDate endDate) {
//        String sql = "select *from t_product where 1=1 ";
//        String str = "";
//        if (name != null && name.length() != 0) {
//            str += "and like '%'+name+'%'";
//        }
//        if (startDate != null) {
//            str += "and p_createDate>= '" + startDate + "'";
//        }
//        if (endDate != null) {
//            str = "and p_createDate<= '" + endDate + "'";
//        }
//
//        CutPageBean cutPage = new CutPageBean();
//        this.setConnection();
//        try {
//            ps = con.prepareStatement(sql + str + " limit ?,?");
//            ps.setObject(1, (pageNO - 1) * CutPageBean.PAGESIZE);
//            ps.setObject(2, CutPageBean.PAGESIZE);
//
//            rs = ps.executeQuery();
//            while (rs.next()) {
//                cutPage.getList().add(this.getProduct());
//            }
//
//            ps = con.prepareStatement("select  count(*) c from t_product where 1=1 " + str);
//            rs = ps.executeQuery();
//            if (rs.next()) {
//                cutPage.setCount(rs.getInt("c"));
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        } finally {
//            this.closeConnection();
//        }

        CutPageBean cutPage = new CutPageBean();
        List valueList = new ArrayList();
        String dynaSql = "";
        if (name != null && name != "") {
            valueList.add("%" + name + "%");
            dynaSql += " and p_name like ?";
        }
        if (startDate != null) {
            valueList.add(startDate);
            dynaSql += " and p_createDate >= ?";
        }
        if (endDate != null) {
            valueList.add(endDate);
            dynaSql += " and p_createDate <= ?";
        }
        this.setConnection();
        try {
            ps = con.prepareStatement("select * from t_product where 1=1 " + dynaSql + " limit ?,?");
            for (int i = 0; i < valueList.size(); i++) {
                ps.setObject(i + 1, valueList.get(i));
            }
            ps.setObject(valueList.size() + 1, (pageNO - 1) * CutPageBean.PAGESIZE);
            ps.setObject(valueList.size() + 2, CutPageBean.PAGESIZE);

            rs = ps.executeQuery();
            while (rs.next()) {
                cutPage.getList().add(this.getProduct());
            }

            ps = con.prepareStatement("select  count(*) c from t_product where 1=1 " + dynaSql);
            for (int i = 0; i < valueList.size(); i++) {
                ps.setObject(i + 1, valueList.get(i));
            }
            rs = ps.executeQuery();
            if (rs.next()) {
                cutPage.setCount(rs.getInt("c"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return cutPage;
    }

    @Override
    public ProductBean findById(Integer id) {
        ProductBean product = null;
        this.setConnection();
        try {
            ps = con.prepareStatement("select * from t_product where pk_productId=?");
            ps.setObject(1,id);

            rs = ps.executeQuery();
            if(rs.next()){
                product = this.getProduct();
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            this.closeConnection();
        }

        return product;
    }

    @Override
    public void update(Integer id, Integer price) {
        this.setConnection();
        try {
            ps = con.prepareStatement("update t_product set p_price=? where pk_productId=?");
            ps.setObject(1, price);
            ps.setObject(2, id);

            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
    }

    @Override
    public void del(Integer id) {
        this.setConnection();
        try {
            this.ps = this.con.prepareStatement("DELETE from t_product where pk_productId=?");
            ps.setObject(1, id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConnection();
        }
    }

    private ProductBean getProduct() {
        ProductBean product = new ProductBean();
        try {
            product.setId(rs.getInt("pk_productId"));
            product.setName(rs.getString("p_name"));
            product.setCreateDate(LocalDate.parse(rs.getString("p_createDate")));
            product.setPrice(rs.getInt("p_price"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return product;
    }
}
